package xml.resource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.restlet.data.Status;
import org.restlet.representation.Representation;
import org.restlet.resource.Delete;
import org.restlet.resource.Get;
import org.restlet.resource.Put;
import org.restlet.resource.ResourceException;
import xml.form.Form;
import xml.model.Adresse;
import xml.model.Contact;
import xml.model.Email;
import xml.model.Telephone;
import xml.model.Type;
import xml.validator.LengthValidator;
import xml.validator.LongRangeValidator;
import xml.validator.NotEmptyValidator;
import xml.validator.RegexValidator;

/**
 * Resource représentant un contact
 * Méthodes: GET, DELETE, PUT
 * @author marya
 */
public class ContactResource extends AbstractResource {

    private Contact contact;

    public ContactResource() {
        super();
    }

    @Override
    protected void doInit() throws ResourceException {
        Long idContact;
        Connection connexion = null;
        final String sqlContact = "SELECT * FROM contact WHERE id_contact = ";
        final String sqlTel = "SELECT tel.value, type.libelle FROM tel "
                + "INNER JOIN type ON type.id_type = tel.id_type "
                + "WHERE tel.id_contact = ";
        final String sqlEmail = "SELECT email.value, type.libelle FROM email "
                + "INNER JOIN type ON type.id_type = email.id_type "
                + "WHERE email.id_contact = ";
        String idContactAttribute = getRequest().getAttributes().get("idContact").toString();
        LongRangeValidator longRangeValidator = new LongRangeValidator(1l, null);
        if (longRangeValidator.isValid(idContactAttribute)) {
            idContact = Long.parseLong(idContactAttribute);
        } else {
            throw new ResourceException(Status.CLIENT_ERROR_BAD_REQUEST, "invalidIdContact");
        }

        try {
            connexion = database.getNewConnection();
            ResultSet rs = connexion.createStatement().executeQuery(sqlContact + idContact);
            if (rs != null && rs.next()) {
                contact = new Contact(
                        rs.getString("nom"),
                        rs.getString("prenom"),
                        new Adresse(
                        rs.getString("voie"),
                        rs.getString("ville"),
                        rs.getString("code_postal"),
                        rs.getString("pays")));
                contact.setIdContact(rs.getInt("id_contact"));
                rs = connexion.createStatement().executeQuery(sqlTel + idContact);
                while (rs.next()) {
                    contact.getTels().add(new Telephone(Type.getType(rs.getString("libelle")), rs.getString("value")));
                }
                rs = connexion.createStatement().executeQuery(sqlEmail + idContact);
                while (rs.next()) {
                    contact.getEmails().add(new Email(Type.getType(rs.getString("libelle")), rs.getString("value")));
                }
                DbUtils.closeQuietly(rs);
                DbUtils.closeQuietly(connexion);
                contact.setUri(getRootRef().getHostIdentifier() + "/contacts/" + contact.getIdContact());
            } else {
                DbUtils.closeQuietly(connexion);
                setExisting(false);
            }

        } catch (SQLException e) {
            DbUtils.rollbackAndCloseQuietly(connexion);
            throw new ResourceException(Status.SERVER_ERROR_INTERNAL, e);
        }
    }

    @Get
    public Representation doGet() throws Exception {
        return contact.toXML();
    }

    @Delete
    public void doDelete() throws Exception {
        final String sqlGroupe = "DELETE FROM contact_groupe WHERE id_contact = " + contact.getIdContact();
        final String sqlTel = "DELETE FROM tel WHERE id_contact = " + contact.getIdContact();
        final String sqlEmail = "DELETE FROM email WHERE id_contact = " + contact.getIdContact();
        final String sqlContact = "DELETE FROM contact WHERE id_contact = " + contact.getIdContact();
        Connection connexion = null;
        try {
            connexion = database.getNewConnection();
            connexion.setAutoCommit(false);
            QueryRunner queryRunner = new QueryRunner();

            queryRunner.update(connexion, sqlGroupe);
            queryRunner.update(connexion, sqlTel);
            queryRunner.update(connexion, sqlEmail);
            int res = queryRunner.update(connexion, sqlContact);
            if (res < 1) {
                DbUtils.rollbackAndCloseQuietly(connexion);
                setStatus(Status.SERVER_ERROR_INTERNAL, "Impossible de supprimer la ressource représentant ce contact");
            } else {
                connexion.commit();
                DbUtils.closeQuietly(connexion);
                setStatus(Status.SUCCESS_NO_CONTENT);
            }

        } catch (SQLException e) {
            DbUtils.rollbackAndCloseQuietly(connexion);
            throw e;
        }
    }

    @Put
    public Representation doPut(Representation entity) throws Exception {
        Representation response = null;
        Connection connexion = null;
        final String sqlContact = "UPDATE contact SET nom = ?,prenom = ?,voie = ?,"
                + "ville = ?,code_postal = ?,pays = ? "
                + "WHERE id_contact = " + contact.getIdContact();
        final String updateTel = " UPDATE tel SET value = ?  WHERE id_telephone = ?";
        final String updateEmail = " UPDATE email SET value = ?  WHERE id_email = ?";
        final String sqlTel = "INSERT INTO tel(value,id_contact,id_type) VALUES (?,?,?)";
        final String sqlEmail = "INSERT INTO email(value,id_contact,id_type) VALUES (?,?,?)";
        Form form = getContactForm(entity);
        if (!form.isValid()) {
            for (String error : form.getMessages()) {
                errors.add(new ResourceError(error));
                setStatus(Status.CLIENT_ERROR_BAD_REQUEST);
            }
        } else {
            System.out.println("C'est valide");
            try {
                connexion = database.getNewConnection();
                connexion.setAutoCommit(false);
                QueryRunner queryRunner = new QueryRunner();
                String nom = form.getFirstValue("nom");
                if (nom == null || nom.trim().isEmpty()) {
                    nom = contact.getNom();
                }

                String prenom = form.getFirstValue("prenom");
                if (prenom == null || prenom.trim().isEmpty()) {
                    prenom = contact.getPrenom();
                }

                String voie = form.getFirstValue("voie");
                if (voie == null || voie.trim().isEmpty()) {
                    voie = contact.getAdresse().getVoie();
                }

                String ville = form.getFirstValue("ville");
                if (ville == null || ville.trim().isEmpty()) {
                    ville = contact.getAdresse().getVille();
                }

                String codePosTal = form.getFirstValue("codePostal");
                if (codePosTal == null || codePosTal.trim().isEmpty()) {
                    codePosTal = contact.getAdresse().getCodePostal();
                }

                String pays = form.getFirstValue("pays");
                if (pays == null || pays.trim().isEmpty()) {
                    pays = contact.getAdresse().getPays();
                }

                Integer idContact = queryRunner.update(connexion, sqlContact,
                        nom,
                        prenom,
                        voie,
                        ville,
                        codePosTal,
                        pays);

                String telHome = form.getFirstValue("telHome");
                Integer iTelHome = 1;

                String telPrivate = form.getFirstValue("telPrivate");
                Integer iTelPrivate = 1;

                String telWork = form.getFirstValue("telWork");
                Integer iTelWork = 1;

                String emailHome = form.getFirstValue("emailHome");
                Integer iEmailHome = 1;

                String emailPrivate = form.getFirstValue("emailPrivate");
                Integer iEmailPrivate = 1;

                String emailWork = form.getFirstValue("emailWork");
                Integer iEmailWork = 1;

                if (telHome != null && !telHome.trim().isEmpty()) {
                    if (contact.hasTel(Type.HOME)) {
                        iTelHome = queryRunner.update(connexion, updateTel, telHome, getIdTel(Type.HOME, contact));
                    } else {
                        iTelHome = queryRunner.update(connexion, sqlTel, telHome, contact.getIdContact(), 1);
                    }
                }

                if (telPrivate != null && !telPrivate.trim().isEmpty()) {
                    if (contact.hasTel(Type.PRIVATE)) {
                        iTelPrivate = queryRunner.update(connexion, updateTel, telPrivate, getIdTel(Type.PRIVATE, contact));
                    } else {
                        iTelPrivate = queryRunner.update(connexion, sqlTel, telPrivate, contact.getIdContact(), 2);
                    }
                }

                if (telWork != null && !telWork.trim().isEmpty()) {
                    if (contact.hasTel(Type.WORK)) {
                        iTelWork = queryRunner.update(connexion, updateTel, telWork, getIdTel(Type.WORK, contact));
                    } else {
                        iTelWork = queryRunner.update(connexion, sqlTel, telWork, contact.getIdContact(), 3);
                    }
                }

                if (emailHome != null && !emailHome.trim().isEmpty()) {
                    if (contact.hasEmail(Type.HOME)) {
                        iEmailHome = queryRunner.update(connexion, updateEmail, emailHome, getIdEmail(Type.HOME, contact));
                    } else {
                        iEmailHome = queryRunner.update(connexion, sqlEmail, emailHome, contact.getIdContact(), 1);
                    }
                }


                if (emailPrivate != null && !emailPrivate.trim().isEmpty()) {
                    if (contact.hasEmail(Type.PRIVATE)) {
                        iEmailPrivate = queryRunner.update(connexion, updateEmail, emailPrivate, getIdEmail(Type.PRIVATE, contact));
                    } else {
                        iEmailPrivate = queryRunner.update(connexion, sqlEmail, emailPrivate, contact.getIdContact(), 2);
                    }
                }

                if (emailWork != null && !emailWork.trim().isEmpty()) {
                    if (contact.hasEmail(Type.WORK)) {
                        iEmailWork = queryRunner.update(connexion, updateEmail, emailWork, getIdEmail(Type.WORK, contact));
                    } else {
                        iEmailWork = queryRunner.update(connexion, sqlEmail, emailWork, contact.getIdContact(), 3);
                    }
                }

                if (idContact != null && idContact > 0
                        && iTelHome != null && iTelHome > 0
                        && iTelPrivate != null && iTelPrivate > 0
                        && iTelWork != null && iTelWork > 0
                        && iEmailHome != null && iEmailHome > 0
                        && iEmailPrivate != null && iEmailPrivate > 0
                        && iEmailWork != null && iEmailWork > 0) {
                    connexion.commit();
                    Contact result = new Contact(contact.getIdContact());
                    result.setUri(getRootRef().getHostIdentifier() + "/contacts/" + contact.getIdContact());
                    response = result.toXML();
                } else {
                    DbUtils.rollbackAndCloseQuietly(connexion);
                    setStatus(Status.SERVER_ERROR_INTERNAL, "Impossible to create contact");
                }
            } catch (SQLException e) {
                DbUtils.rollbackAndCloseQuietly(connexion);
                setStatus(Status.SERVER_ERROR_INTERNAL, "Impossible to create contact");
                throw e;
            }
        }

        if (!errors.isEmpty()) {
            response = ResourceError.getListRepresentation(errors);
        }
        return response;
    }

    public Form getContactForm(Representation entity) {
        Form form = new Form(entity);
        NotEmptyValidator notEmptyValidator = new NotEmptyValidator();
        LengthValidator lengthValidator = new LengthValidator(255);
        LengthValidator shortLengthValidator = new LengthValidator(13);
        RegexValidator namesValidator = new RegexValidator("^([a-zA-Z'àâéèêëîïôùûçÀÂÉÈÔÙÛÇ\\s-]{1,100})$");
        RegexValidator telValidator = new RegexValidator("^0[1-6]{1}(([0-9]{2}){4})|((\\s[0-9]{2}){4})|((-[0-9]{2}){4})$");
        RegexValidator emailValidator = new RegexValidator("^[a-zA-Z0-9_.-]+@[a-zA-Z0-9-]+.[a-zA-Z0-9-.]+$");

        if (form.getFirstValue("nom") != null) {
            form.addValidators("nom", notEmptyValidator, lengthValidator, namesValidator);
        }

        if (form.getFirstValue("prenom") != null) {
            form.addValidators("prenom", notEmptyValidator, lengthValidator, namesValidator);
        }

        if (form.getFirstValue("voie") != null) {
            form.addValidators("voie", notEmptyValidator, lengthValidator);
        }

        if (form.getFirstValue("ville") != null) {
            form.addValidators("ville", notEmptyValidator, lengthValidator);
        }

        if (form.getFirstValue("codePostal") != null) {
            form.addValidators("codePostal", notEmptyValidator, new LengthValidator(5));
        }

        if (form.getFirstValue("pays") != null) {
            form.addValidators("pays", notEmptyValidator, lengthValidator);
        }

        if (form.getFirstValue("emailWork") != null) {
            form.addValidators("emailWork", notEmptyValidator, lengthValidator, emailValidator);
        }

        if (form.getFirstValue("emailHome") != null) {
            form.addValidators("emailHome", notEmptyValidator, lengthValidator, emailValidator);
        }

        if (form.getFirstValue("emailPrivate") != null) {
            form.addValidators("emailPrivate", notEmptyValidator, lengthValidator, emailValidator);
        }

        if (form.getFirstValue("telWork") != null) {
            form.addValidators("telWork", notEmptyValidator, shortLengthValidator, telValidator);
        }

        if (form.getFirstValue("telHome") != null) {
            form.addValidators("telHome", notEmptyValidator, shortLengthValidator, telValidator);
        }

        if (form.getFirstValue("telPrivate") != null) {
            form.addValidators("telPrivate", notEmptyValidator, shortLengthValidator, telValidator);
        }

        return form;
    }

    private Integer getIdTel(Type type, Contact c) throws SQLException {
        Integer result = null;
        String sql = "SELECT id_telephone FROM tel WHERE "
                + "id_contact = " + c.getIdContact() + " AND id_type = ("
                + "SELECT id_type FROM type WHERE libelle = '" + type.toString() + "' )";
        Connection cx = database.getNewConnection();
        ResultSet rs = cx.createStatement().executeQuery(sql);
        if (rs.next()) {
            result = rs.getInt("id_telephone");
        }
        cx.close();
        return result;
    }

    private Integer getIdEmail(Type type, Contact c) throws SQLException {
        Integer result = null;
        String sql = "SELECT id_email FROM email WHERE "
                + "id_contact = " + c.getIdContact() + " AND id_type = ("
                + "SELECT id_type FROM type WHERE libelle = \"" + type.toString() + "\" )";
        Connection cx = database.getNewConnection();
        ResultSet rs = cx.createStatement().executeQuery(sql);
        if (rs.next()) {
            result = rs.getInt("id_email");
        }
        cx.close();
        return result;
    }
}
